package drugdealer.persistencia;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import drugdealer.dados.Funcionario;
import drugdealer.dados.Paciente;
import drugdealer.persistencia.exceptions.*;

public class FuncionarioPersistence extends PacientePersistence implements IFuncionarioPersistence {
	public static String TABLENAME = "funcionario";
	
	public FuncionarioPersistence(DDPersistence owner) {
		super(owner);
		this.tableName = FuncionarioPersistence.TABLENAME;
		this.primaryKey = FuncionarioPersistence.PRIMARYKEY;
		this.createTableStmt = "CREATE TABLE IF NOT EXISTS " + 
			FuncionarioPersistence.TABLENAME + "(" +
			FuncionarioPersistence.PRIMARYKEY + " varchar(11) PRIMARY KEY NOT NULL," +
			"senha varchar(20)," +
			"salario real" +
			")";
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IFuncionarioPersistence#get(java.lang.String)
	 */
	public Funcionario get(String cpf) {
		Funcionario func = null;
		String selectFunc = "SELECT * FROM " + FuncionarioPersistence.TABLENAME + 
				" WHERE cpf='" + cpf + "'";
		
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectFunc);
			if (rs.next())
				func = new Funcionario(super.get(cpf), rs.getString("senha"),
						rs.getDouble("salario"));
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return func;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IFuncionarioPersistence#getList()
	 */
	public Vector<String[]> getList() {
		Vector<String[]> list = new Vector<String[]>();
		String selectList = "SELECT q." + FuncionarioPersistence.PRIMARYKEY + 
			",p.nome FROM (SELECT " + PacientePersistence.PRIMARYKEY + 
			" FROM " + PacientePersistence.TABLENAME + " EXCEPT SELECT " + 
			MedicoPersistence.PRIMARYKEY + " FROM " + MedicoPersistence.TABLENAME +
			") q, " + FuncionarioPersistence.TABLENAME + " f, " + 
			PacientePersistence.TABLENAME + " p " +
			"WHERE " +
			"q." + FuncionarioPersistence.PRIMARYKEY + "=" +
			"f." + FuncionarioPersistence.PRIMARYKEY  + " AND " +
			"q." + FuncionarioPersistence.PRIMARYKEY + "=" +
			"p." +	PacientePersistence.PRIMARYKEY;
		
		System.out.println(selectList);
		
		String[] pair;
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectList);
			
			while (rs.next()) {
				pair = new String[2];
				pair[0] = rs.getString(PacientePersistence.PRIMARYKEY);
				pair[1] = rs.getString("nome");
				list.add(pair);
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IFuncionarioPersistence#add(drugdealer.dados.Funcionario)
	 */
	public void add(Funcionario func) throws AlreadyExistsException {
		super.add((Paciente) func);
		super.add("INSERT INTO " + FuncionarioPersistence.TABLENAME +
				" VALUES ('" + func.getCPF() + "','" + func.getSenha() + "'," +
				func.getSalario()+ ")");
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IFuncionarioPersistence#update(drugdealer.dados.Funcionario)
	 */
	public void update(Funcionario func) throws DoesNotExistsException {
		super.update((Paciente) func);
		super.update("UPDATE " + FuncionarioPersistence.TABLENAME +
				" SET senha='" + func.getSenha() + "'," +
				"salario=" + func.getSalario() +
				" WHERE " + FuncionarioPersistence.PRIMARYKEY + "='" + 
				func.getCPF() + "'");
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IFuncionarioPersistence#remove(drugdealer.dados.Funcionario)
	 */
	public void remove(Funcionario funcionario) {
		super.remove(FuncionarioPersistence.TABLENAME, 
				FuncionarioPersistence.PRIMARYKEY, funcionario.getCPF());
		super.remove((Paciente) funcionario);
	}
}
